package maa.dao.h2;

import maa.dao.DAOException;
import maa.dao.DAOManager;
import maa.dao.DAOCommand;
import maa.entity.Gender;
import maa.entity.Mentor;
import maa.entity.Role;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class MentorDAOimpl implements DAOCommand {
    private final Logger logger = LoggerFactory.getLogger(MentorDAOimpl.class);
    private final String FIND_ALL = "SELECT users.*, mentors.* from users inner join mentors " +
            "on users.userid = mentors.mentorid";
    private final String UPDATE_MENTOR = "Update mentors set work_experience =?, rang = ?, info =? where mentorid=?";
    private final String UPDATE_MENTOR_USER = "UPDATE users SET username =?, surname = ?,email =?,password=?," +
            " birthdate = ?, gender =? where userid=?";
    private final String FIND_BY_ID = "SELECT users.*, mentors.* from users inner join mentors " +
            "on users.userid = mentors.mentorid where mentorid = ?";
    private final String CREATE_MENTOR = "INSERT INTO mentors(mentorid, work_experience, rang, info) values (?,?,?,?)";
    private final String DELETE_MENTOR = "Update mentors set deleted='1' where mentorid = ?";
    private final String RESTORE_MENTOR = "Update mentors set deleted='0' where mentorid = ?";
    private Connection connection;

    public MentorDAOimpl() {
    }

    public MentorDAOimpl(Connection connection) {

        this.connection = connection;
    }

    public Boolean update(Mentor mentor) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            PreparedStatement statement = null;
            try {
                preparedStatement = connection.prepareStatement(UPDATE_MENTOR);
                preparedStatement.setDouble(1, mentor.getWorkExperience());
                preparedStatement.setString(2, mentor.getRang());
                preparedStatement.setString(3, mentor.getInfo());
                preparedStatement.setObject(4, mentor.getUuid());

                statement = connection.prepareStatement(UPDATE_MENTOR_USER);
                statement.setString(1, mentor.getName());
                statement.setString(2, mentor.getSurname());
                statement.setString(3, mentor.getEmail());
                statement.setString(4, mentor.getPassword());
                statement.setDate(5, mentor.getBirthdate());
                statement.setString(6, String.valueOf(mentor.getGender()));
                statement.setObject(7, mentor.getUuid());

                statement.execute();
                res = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                logger.error("Could not update mentor " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) return true;
        else return false;
    }

    public Boolean restore(UUID uuid) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(RESTORE_MENTOR);
                preparedStatement.setObject(1, uuid);
                res = preparedStatement.executeUpdate();

            } catch (SQLException e) {
                logger.error("Could not delete mentor " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) {
            logger.debug("Deleted user with id: " + uuid.toString());
            return true;
        } else {
            logger.debug("Failed to delete user with id: " + uuid.toString());
            return false;
        }
    }

    public Boolean delete(UUID uuid) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(DELETE_MENTOR);
                preparedStatement.setObject(1, uuid);
                res = preparedStatement.executeUpdate();

            } catch (SQLException e) {
                logger.error("Could not delete mentor " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) {
            logger.debug("Deleted user with id: " + uuid.toString());
            return true;
        } else {
            logger.debug("Failed to delete user with id: " + uuid.toString());
            return false;
        }
    }

    public Boolean create(Mentor mentor) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(CREATE_MENTOR);
                preparedStatement.setObject(1, mentor.getUuid());
                preparedStatement.setDouble(2, mentor.getWorkExperience());
                preparedStatement.setString(3, mentor.getRang());
                preparedStatement.setString(4, mentor.getInfo());

                res = preparedStatement.executeUpdate();

            } catch (SQLException e) {
                logger.error("Could not create mentor " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) return true;
        else return false;
    }

    @Override
    public Object execute(DAOManager daoManager) {
        return new Object();
    }

    public List<Mentor> findAll() throws DAOException {
        List<Mentor> list = new ArrayList<>();
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(FIND_ALL);
                ResultSet set = preparedStatement.executeQuery();
                while (set.next()) {
                    Mentor mentor = createEntity(set);
                    list.add(mentor);
                }
            } catch (SQLException e) {
                logger.error("Could not find all mentors " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        return list;
    }

    private Mentor createEntity(ResultSet resultSet) throws SQLException {
        Mentor mentor = new Mentor();
        mentor.setUuid((UUID) resultSet.getObject("userid"));
        mentor.setName(resultSet.getString("username"));
        mentor.setSurname(resultSet.getString("surname"));
        mentor.setRole(Role.valueOf(resultSet.getString("role")));
        mentor.setGender(Gender.valueOf(resultSet.getString("gender")));
        mentor.setBirthdate(resultSet.getDate("birthdate"));
        mentor.setEmail(resultSet.getString("email"));
        mentor.setPassword(resultSet.getString("password"));
        mentor.setInfo(resultSet.getString("info"));
        mentor.setRang(resultSet.getString("rang"));
        mentor.setWorkExperience(resultSet.getDouble("work_experience"));
        int i = resultSet.getByte("deleted");
        if(i==1)mentor.setDeleted(true);
        else mentor.setDeleted(false);
        return mentor;
    }

    public Mentor findById(UUID uuid) throws DAOException {
        Mentor mentor = new Mentor();
        if (connection != null && uuid != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(FIND_BY_ID);
                preparedStatement.setObject(1, uuid);
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    mentor = createEntity(resultSet);
                }
            } catch (SQLException e) {
                logger.error("Could not find mentor by id " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        return mentor;
    }

    public void close(PreparedStatement statement) {
        try {
            statement.close();
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
    }
}
